package org.wpahs.ahn.rssfeed.DB;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.util.Log;
import android.webkit.URLUtil;

import org.wpahs.ahn.rssfeed.FeedItem;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * Class to do CRUD on the FeedItemTable
 * Created by dgambone on 01/15/2015.
 */
public class DBFeedItem extends FeedsDB {

    public DBFeedItem(Context context) {
        super(context);
    }

    public int addToDB(org.wpahs.ahn.rssfeed.FeedItem item) throws Exception {
        if(item.getLink() == null || !URLUtil.isValidUrl(item.getLink())){
            throw new Exception("Bad URL or is null");
        }
        int existsId=existsByURL(item.getLink());
        if (existsId == 0) {
            ContentValues cv = toContentValues(item);
            cv.remove(DB.COLUMN_ITEM_ID); // so it generates a new id
            long id = this.getWritableDatabase().insert(
                    DB.TABLE_NAME,
                    null,
                    cv
            );
            if (id > 0) {
                Log.i("FeedItem.addNewItem", "Created item " + String.valueOf(id) + " - " + item.getTitle());
                item.setItemId(0);
                return ((int) id);
            } else {
                throw new Exception("DBFeedItem-addToDB - no new id");
            }
        }else{
            Log.i("FeedItem.addNewItem", "Already exists " + item.getItemId() + " - " + item.getTitle());
        }   return  existsId;

    }

    public boolean isOpen(){
        return getReadableDatabase().isOpen();
    }

    /**
     * used to clear initial bad records
     */
    public void clearEmptyURLs(){
        this.getWritableDatabase().delete(DB.TABLE_NAME,DB.COLUMN_URL + "='' OR " +
        DB.COLUMN_URL + " is null or " + DB.COLUMN_FEED_ID + " = 0 OR " + DB.COLUMN_FEED_ID + " IS NULL",null);
    }


    public ContentValues toContentValues(org.wpahs.ahn.rssfeed.FeedItem item) {
        ContentValues cv = new ContentValues();
        cv.put(DB.COLUMN_ITEM_ID, item.getItemId());
        cv.put(DB.COLUMN_FEED_ID, item.getFeedId());
        cv.put(DB.COLUMN_TITLE, item.getTitle());
        cv.put(DB.COLUMN_DESCRIPTION, item.getDescription());
        cv.put(DB.COLUMN_URL, item.getLink());
        cv.put(DB.COLUMN_PUB_DATE, item.getPubDate());
        cv.put(DB.COLUMN_IMAGE, item.getImage());// make sure it exists
        cv.put(DB.COLUMN_READ, item.getRead());
        cv.put(DB.COLUMN_HASIMAGE,item.getHasImage());
        cv.put(DB.COLUMN_FAVORITE,item.getIsFavorite());
        return cv;
    }

    public int existsByURL(String url) {
        if(url == null || url.equals("")) return 0;
        Cursor cursor = this.getReadableDatabase().query(
                DB.TABLE_NAME,
                new String[]{DB.COLUMN_ITEM_ID},
                DB.COLUMN_URL + "=?",
                new String[]{url},
                null, null, null, null);

        if (cursor != null && cursor.getCount() > 0) {
            cursor.moveToFirst();
            int id = cursor.getInt(0);
            cursor.close();
            return id;
        } else {
            return 0;
        }
    }

    public String getImage(int feedItemId){
        Cursor cursor = null;
        try {
            cursor = this.getReadableDatabase().query(
                    DB.TABLE_NAME,
                    new String[]{DB.COLUMN_IMAGE},
                    DB.COLUMN_ITEM_ID + "=?",
                    new String[]{String.valueOf(feedItemId)},
                    null, null, null, null);

            if (cursor != null && cursor.getCount() > 0) {
                cursor.moveToFirst();
                String image = cursor.getString(0);
                cursor.close();
                return image;
            } else {
                return null;
            }
        }catch (IllegalStateException isEx){
            Log.e(this.getClass().getName(),
                    "getImage: " + (isEx.getMessage() != null ? isEx.getMessage() : "Mystery Error"));

        }catch (OutOfMemoryError OOMEx){
            OOMEx.printStackTrace();
        }finally {
            if(cursor != null && !cursor.isClosed()){
                cursor.close();
            }
        }
        return null;

    }

    public void setImage(int feedItemId, String image) throws Exception {
        if(feedItemId == 0){
            throw new Exception("feedItemId is required");
        }
        ContentValues cv = new ContentValues();
        cv.put(DB.COLUMN_IMAGE,image);
        cv.put(DB.COLUMN_HASIMAGE,1);
        long recordsUpdated = this.getWritableDatabase().update(
                DB.TABLE_NAME,
                cv,
                DB.COLUMN_ITEM_ID + "=?",
            new String[] {String.valueOf(feedItemId)}
            );
            if (recordsUpdated > 0) {
                if(image != null){
                    Log.i("FeedItem.setImage", "Set image for " + feedItemId + " of size " + image.length());

                }else{
                    Log.i("FeedItem.setImage", "Set image for " + feedItemId + " it's NULL");
                }
            } else {
                Log.i("FeedItem.setImage", "Id was not set");

            }
    }

    public void setRead(int feedItemId, Boolean isRead){
        ContentValues cv = new ContentValues();
        cv.put(DB.COLUMN_READ,isRead);
        long recordsUpdated = this.getWritableDatabase().update(
                DB.TABLE_NAME,
                cv,
                DB.COLUMN_ITEM_ID + "=?",
                new String[]{String.valueOf(feedItemId)}
        );
        if (recordsUpdated > 0) {
            Log.i("FeedItem.setRead", "Set read ("+isRead.toString()+ ") for " + feedItemId);
        } else {
            Log.i("FeedItem.setRead", "Id was not set");

        }
    }

    public void setFavorite(int feedItemId, Boolean isFavorite){
        ContentValues cv = new ContentValues();
        cv.put(DB.COLUMN_FAVORITE,isFavorite);
        long recordsUpdated = this.getWritableDatabase().update(
                DB.TABLE_NAME,
                cv,
                DB.COLUMN_ITEM_ID + "=?",
                new String[] {String.valueOf(feedItemId)}
        );
        if (recordsUpdated > 0) {
            Log.i("FeedItem.setFavorite", "Set favorite ("+isFavorite.toString()+ ") for " + feedItemId);
        } else {
            Log.i("FeedItem.setFavorite", "Id was not set");

        }
    }

    public List<FeedItem>  getAllItems(Boolean read){
        String torF = "0";
        if (read) torF="1";

        List<FeedItem> retval = new ArrayList<>();
        Cursor cursor = this.getReadableDatabase().query(
                DB.TABLE_NAME,
                new String[]{
                        DB.COLUMN_ITEM_ID,
                        DB.COLUMN_FEED_ID,
                        DB.COLUMN_TITLE,
                        DB.COLUMN_DESCRIPTION,
                        DB.COLUMN_URL,
                        DB.COLUMN_PUB_DATE,
                        DB.COLUMN_READ,
                        DB.COLUMN_HASIMAGE,
                        DB.COLUMN_FAVORITE
                },
                DB.COLUMN_READ + "=?",
                new String[]{torF},
                null,
                null,
                DB.COLUMN_PUB_DATE + " DESC",
                null
        );

        if (cursor != null && cursor.getCount() > 0) {
            while(cursor.moveToNext()){
                FeedItem fi = new FeedItem();
                fi.setItemId(cursor.getInt(0));
                fi.setFeedId(cursor.getInt(1));
                fi.setTitle(cursor.getString(2));
                fi.setDescription(cursor.getString(3));
                fi.setLink(cursor.getString(4));
                fi.setPubDate(cursor.getString(5));
                fi.setRead((cursor.getInt(6) == 1));
                fi.setHasImage((cursor.getInt(7)==1));
                fi.setIsFavorite((cursor.getInt(8) == 1));
                retval.add(fi);
            }
        }
        if (cursor !=null) cursor.close();
        return retval;
    }

    public List<FeedItem>  getAllFavorites(){
        String torF = "0";

        List<FeedItem> retval = new ArrayList<>();
        Cursor cursor = this.getReadableDatabase().query(
                DB.TABLE_NAME,
                new String[]{
                        DB.COLUMN_ITEM_ID,
                        DB.COLUMN_FEED_ID,
                        DB.COLUMN_TITLE,
                        DB.COLUMN_DESCRIPTION,
                        DB.COLUMN_URL,
                        DB.COLUMN_PUB_DATE,
                        DB.COLUMN_READ,
                        DB.COLUMN_HASIMAGE,
                        DB.COLUMN_FAVORITE
                },
                DB.COLUMN_FAVORITE + "=1",
                null,
                null,
                null,
                DB.COLUMN_PUB_DATE + " DESC",
                null
        );

        if (cursor != null && cursor.getCount() > 0) {
            while(cursor.moveToNext()){
                FeedItem fi = new FeedItem();
                fi.setItemId(cursor.getInt(0));
                fi.setFeedId(cursor.getInt(1));
                fi.setTitle(cursor.getString(2));
                fi.setDescription(cursor.getString(3));
                fi.setLink(cursor.getString(4));
                fi.setPubDate(cursor.getString(5));
                fi.setRead((cursor.getInt(6) == 1));
                fi.setHasImage((cursor.getInt(7)==1));
                fi.setIsFavorite((cursor.getInt(8) == 1));
                retval.add(fi);
            }
        }
        if (cursor !=null) cursor.close();
        return retval;
    }

    public List<FeedItem>  getItemsByFeed(int feedId, boolean read){
        String torF = "0";
        if (read) torF="1";

        List<FeedItem> retval = new ArrayList<>();
        Cursor cursor = this.getReadableDatabase().query(
                DB.TABLE_NAME,
                new String[]{
                        DB.COLUMN_ITEM_ID,
                        DB.COLUMN_FEED_ID,
                        DB.COLUMN_TITLE,
                        DB.COLUMN_DESCRIPTION,
                        DB.COLUMN_URL,
                        DB.COLUMN_PUB_DATE,
                        DB.COLUMN_READ,
                        DB.COLUMN_HASIMAGE,
                        DB.COLUMN_FAVORITE
                },
                DB.COLUMN_READ + "=? AND " + DB.COLUMN_FEED_ID + "=?",
                new String[]{torF, String.valueOf(feedId)},
                null,
                null,
                DB.COLUMN_PUB_DATE + " DESC",
                null
        );

        if (cursor != null && cursor.getCount() > 0) {
            while(cursor.moveToNext()){
                FeedItem fi = new FeedItem();
                fi.setItemId(cursor.getInt(0));
                fi.setFeedId(cursor.getInt(1));
                fi.setTitle(cursor.getString(2));
                fi.setDescription(cursor.getString(3));
                fi.setLink(cursor.getString(4));
                fi.setPubDate(cursor.getString(5));
                fi.setRead((cursor.getInt(6) == 1));
                fi.setHasImage((cursor.getInt(7) == 1));
                fi.setIsFavorite((cursor.getInt(8) == 1));
                retval.add(fi);
            }
        }
        if(cursor != null)cursor.close();
        return retval;
    }

    public FeedItem getById(int feedItemId){

        FeedItem retval = null;
        Cursor cursor = this.getReadableDatabase().query(
                DB.TABLE_NAME,
                new String[]{
                        DB.COLUMN_ITEM_ID,
                        DB.COLUMN_FEED_ID,
                        DB.COLUMN_TITLE,
                        DB.COLUMN_DESCRIPTION,
                        DB.COLUMN_URL,
                        DB.COLUMN_PUB_DATE,
                        DB.COLUMN_IMAGE,
                        DB.COLUMN_READ,
                        DB.COLUMN_HASIMAGE,
                        DB.COLUMN_FAVORITE
                },
                DB.COLUMN_ITEM_ID + "=?",
                new String[]{String.valueOf(feedItemId)},
                null,
                null,
                null,
                null
        );

        if (cursor != null && cursor.getCount() > 0) {
            cursor.moveToFirst();
                retval = new FeedItem();
                retval.setItemId(cursor.getInt(0));
                retval.setFeedId(cursor.getInt(1));
                retval.setTitle(cursor.getString(2));
                retval.setDescription(cursor.getString(3));
                retval.setLink(cursor.getString(4));
                retval.setPubDate(cursor.getString(5));
                retval.setImage(cursor.getString(6));
                retval.setRead((cursor.getInt(7) == 1));
                retval.setHasImage((cursor.getInt(8) == 1));
                retval.setIsFavorite((cursor.getInt(9) == 1));

        }
        if(cursor != null)cursor.close();
        return retval;
    }

    public void clearTable(){
        int rowsDeleted = this.getWritableDatabase().delete(DB.TABLE_NAME,null,null);
        Log.i("DBFeedItem-clearTable",String.valueOf(rowsDeleted) + " rows deleted");
    }

    /*
    Just set the images to null so we can save space.  We can no longer view them
    But we can't delete them either.  If we delete the entire feed item row we'll
    see feed items reappear in the GUI.
     */
    public void clearTableOfReadImages(){
        try{
            ContentValues cv = new ContentValues();
            cv.put(DB.COLUMN_IMAGE,"");
            int imagesSetToNull = getWritableDatabase().update(
                    DB.TABLE_NAME,
                    cv,
                    DB.COLUMN_READ + " = 1 and " + DB.COLUMN_IMAGE + " <> '' and " +
                    DB.COLUMN_FAVORITE + " = 0",
                    null);

           Log.i("clearTableOfReadImages",String.valueOf(imagesSetToNull));
        }catch (Exception ex){
            Log.e("clearTableOfReadImages",
                    (ex.getMessage()==null ? "ex message was null" : ex.getMessage())
            );
        }
    }


    public void clearTableOfXDayOldItems(int days){
        Date now = new Date();
        long xDaysAgo = now.getTime() - (long)days*1000*60*60*24;
        Date xD = new Date(xDaysAgo);
        Log.i("DBFeedItem", xD.toString() +
                " - " +
                String.valueOf(xDaysAgo)
        );

        int rowsDeleted = this.getWritableDatabase().delete(DB.TABLE_NAME,
                "(" +
                        "("+ DB.COLUMN_PUB_DATE + " < ?) or " +
                "("+DB.COLUMN_PUB_DATE + " = '' or " + DB.COLUMN_PUB_DATE + " is null) " +
                ")AND (" + DB.COLUMN_READ + "=1 AND " + DB.COLUMN_FAVORITE + "=0) ",
                new String[] {String.valueOf(xDaysAgo)}
        );

        Log.i("DBFeedItem",String.valueOf(rowsDeleted) + " rows deleted");

    }


    public abstract class DB {

        public static final String TABLE_NAME = "Items";
        public static final String COLUMN_ITEM_ID = "itemid";
        public static final String COLUMN_FEED_ID = "feedid";
        public static final String COLUMN_TITLE = "title";
        public static final String COLUMN_DESCRIPTION = "description";
        public static final String COLUMN_URL = "url";
        public static final String COLUMN_PUB_DATE = "publishedDate";
        public static final String COLUMN_IMAGE = "image";
        public static final String COLUMN_READ = "read";
        public static final String COLUMN_HASIMAGE = "hasimage";
        public static final String COLUMN_FAVORITE = "favorite";

        public static final String SQL_CREATE_TABLE =
                "CREATE TABLE " + TABLE_NAME + " (" +
                        COLUMN_ITEM_ID + " INTEGER PRIMARY KEY autoincrement," +
                        COLUMN_FEED_ID + " INTEGER," +
                        COLUMN_TITLE + " TEXT_TYPE, " +
                        COLUMN_DESCRIPTION + " TEXT_TYPE, " +
                        COLUMN_URL + " TEXT_TYPE, " +
                        COLUMN_PUB_DATE + " TEXT_TYPE, " +
                        COLUMN_IMAGE + " TEXT_TYPE, " +
                        COLUMN_READ + " INTEGER, " +
                        COLUMN_HASIMAGE + " INTEGER, " +
                        COLUMN_FAVORITE + " INTEGER," +
                        "FOREIGN KEY (" + COLUMN_FEED_ID + ") REFERENCES " + DBMyFeed.DB.TABLE_NAME + "(" + DBMyFeed.DB.COLUMN_FEED_ID + ")" +
                        ");";

    }
}
